﻿using System;
using System.Data;
using Antlr.Runtime.Tree;
using MySql.Data.MySqlClient;
//using Net = Web_consultant.Database.Models.Net;
using Web_consultant.Database.Models;
using Web_consultant.Models;
using System.Collections.Generic;


namespace Web_consultant.Database
{
    public class DbConnector
    {
        private readonly MySqlConnectionStringBuilder _mysqlSb = new MySqlConnectionStringBuilder();

        public DbConnector()
        {
            _mysqlSb.Server = "127.0.0.1";
            _mysqlSb.Database = "web-consult";
            _mysqlSb.UserID = "root";
            _mysqlSb.Password = "";

        }

        private DataTable GetQuery(string queryString)
        {
            var dt = new DataTable();

            using (var con = new MySqlConnection())
            {
                con.ConnectionString = _mysqlSb.ConnectionString;

                var com = new MySqlCommand(queryString, con);
                try
                {

                    con.Open();

                    using (MySqlDataReader dr = com.ExecuteReader())
                    {
                        if (dr.HasRows) dt.Load(dr);
                    }
                }
                catch (Exception ex)
                {
                    Console.Out.WriteLine(ex.Message);
                }
            }
         

            return dt;
        }

        public void insertUser(RegisterModel newUser)
        {
            string stringquery;
            if (newUser.idOwner == 0)
            {
                stringquery =
                    @"insert into user (`idUser`, `Name`, `Password`,`LastName`, `Age`, `Sex`, `Role`) values (Null,'" +
                    newUser.Name + "','" + newUser.Password + "','" + newUser.LastName + "','" + newUser.Age + "','"
                    + newUser.Sex + "','" + newUser.Role + "');";
            }
            else
            {
                stringquery =
                    @"insert into user (`idUser`, `Name`, `Password`,`LastName`, `Age`, `Sex`, `Role`, `idOwner`) values (Null,'" +
                    newUser.Name + "','" + newUser.Password + "','" + newUser.LastName + "','" + newUser.Age + "','"
                    + newUser.Sex + "','" + newUser.Role + "','" + newUser.idOwner + "');";
            }
            GetQuery(stringquery);
        }

        public int isUser(LoginModel user)
        {
            string stringquery = @"select idUser FROM user WHERE user.Name='" + user.UserName + "' and user.Password='" + user.Password + "';";
            DataTable dt =GetQuery(stringquery);
            if (dt.Rows != null)
            {
                var a = (int) dt.Rows[0]["idUser"];
                return a;
            }
            return 990;
        }

        public User getUser(int id)
        {
            string stringquery = @"select * FROM user WHERE user.idUser='" + id + "';";
            DataTable dt = GetQuery(stringquery);
            if (dt.Rows != null)
            {
                var user = new User
                {
                    Name = (string) dt.Rows[0]["Name"],
                    LastName = (string) dt.Rows[0]["LastName"],
                    Age = (int) dt.Rows[0]["Age"],
                    Sex = (int)dt.Rows[0]["Sex"],
                    Role = (int)dt.Rows[0]["Role"],
                    UsersId = (List<int>)getSlaves(id)
                };
                return user;
            }
            return null;
        }

        public object getSlaves(int id)
        {
            string stringquery = @"select * FROM user WHERE user.idOwner='" + id + "';";
            DataTable dt = GetQuery(stringquery);
            if (dt.Rows != null)
            {
                var list = new List<int>();
                foreach (DataRow row in dt.Rows)
                {
                    id = (int)row["idUser"];
                    list.Add(id);
                }
                return list;
            }
            return null;
        }

        public void InsertTests(TestResult res, int id)
        {
            string stringQuery = @"SELECT * FROM usertests WHERE idTests=" + res.IdTest + " and idUser=" + id + ";";

            DataTable dt = GetQuery(stringQuery);

            if (dt.Rows.Count > 0)
            {
                if (res.IdTest == 1)
                stringQuery = @"UPDATE usertests SET Result='" + res.Koeff + "' WHERE idTests=" + res.IdTest + " and idUser=" + id + ";";
                else
                {
                    stringQuery = @"UPDATE usertests SET Result='" + res.Res + "' WHERE idTests=" + res.IdTest + " and idUser=" + id + ";";
                }
                GetQuery(stringQuery);
            }
            else
            {
                string stringquery;
                if (res.IdTest == 1)
                    stringquery = @"insert into usertests (`idTests`, `idUser`, `Result`) values (" + res.IdTest + "," + id + ",'" + res.Koeff + "');";
                else
                {
                    stringquery = @"insert into usertests (`idTests`, `idUser`, `Result`) values (" + res.IdTest + "," + id + ",'" + res.Res + "');";
                }
                
                GetQuery(stringquery);
            }
        }

        public void InsertTools(string res, int id)
        {
            string stringQuery = @"SELECT * FROM usertests WHERE idTests=" + 9 + " and idUser=" + id + ";";

            DataTable dt = GetQuery(stringQuery);

            if (dt.Rows.Count > 0)
            {
                string stringquery = @"UPDATE usertests SET Result='" + res + "' WHERE idTests=" + 9 + " and idUser=" + id + ";";
                GetQuery(stringquery);
            }
            else
            {
                string stringquery = @"insert into usertests (`idTests`, `idUser`, `Result`) values (" + 9 + "," + id + ",'" + res + "');";
                GetQuery(stringquery);
            }
        }

        public string GetPassedTests(int id, int idTest)
        {
            string stringQuery = @"SELECT * FROM usertests WHERE idTests=" + idTest + " and idUser=" + id + ";";

            DataTable dt = GetQuery(stringQuery);
            if (dt.Rows.Count > 0)
                return (string)dt.Rows[0]["Result"];
            else return "";
        }

        public int GetAllPassedTests(int id, out int temp, out int eq)
        {
            string stringQuery = @"SELECT * FROM usertests WHERE idTests=" + 7 + " and idUser=" + id + ";";
            int proff;
            DataTable dt = GetQuery(stringQuery);
            if (dt.Rows.Count > 0)
            {
                string prof = (string) dt.Rows[0]["Result"];
                proff = Convert.ToInt32(prof);
            }
            else
            {
                stringQuery = @"SELECT * FROM usertests WHERE idTests=" + 8 + " and idUser=" + id + ";";
                dt = GetQuery(stringQuery);
                if (dt.Rows.Count > 0)
                {
                    string prof = (string)dt.Rows[0]["Result"];
                    proff = Convert.ToInt32(prof);
                }
                else
                {
                    proff = 290;
                }
            }

            stringQuery = @"SELECT * FROM usertests WHERE idTests=" + 5 + " and idUser=" + id + ";";
            dt = GetQuery(stringQuery);
            if (dt.Rows.Count > 0)
            {
                string prof = (string)dt.Rows[0]["Result"];
                temp = Convert.ToInt32(prof);
            }
                else
                {
                    temp = 290;
                }

            stringQuery = @"SELECT * FROM usertests WHERE idTests=" + 4 + " and idUser=" + id + ";";
            dt = GetQuery(stringQuery);
            if (dt.Rows.Count > 0)
            {
                string prof = (string)dt.Rows[0]["Result"];
                eq = Convert.ToInt32(prof);
            }
            else
            {
                eq = 290;
            }

            return proff;
        }



       

//        private Net getNetFromDbRow(DataRow row)
//        {
//            Net net = new Net();
//            net.idNet = (int)row["idNet"];
//            net.Description = (string)row["Description"];
//            net.Name = (string)row["Name"];
//            net.Json = (string)row["Json"];
//            net.Topology_id = (int)row["Topology_id"];
//            return net;
//        }

//        private NetSettings getNetSettingsFromDbRow(DataRow row)
//        {
//            NetSettings netSettings = new NetSettings();
//            netSettings.idNet_Settings = (int)row["idNet_Settings"]; 
//            netSettings.idNet = (int)row["idNet"];
//            netSettings.ThreadsCount = (int)row["ThreadsCount"];
//            netSettings.FrequencyTaskGen = (int)row["FrequencyTaskGen"];
//            netSettings.ModeTaskGen = (int)row["ModeTaskGen"];
//            netSettings.TimeTaskGen = (int)row["TimeTaskGen"];
//            return netSettings;
//        }

//        private Result getResultFromDbRow(DataRow row)
//        {
//            Result result = new Result();
//            result.idResult = (int)row["idResult"];
//            result.idNet = (int)row["idNet"];
//            result.IntensityGen = (int)row["IntensityGen"];
//            result.IntensityService = (int)row["IntensityService"];
//            result.AverageCountOfWorkNodes = (int)row["AverageCountOfWorkNodes"];
//            result.AverageNumberOfTasks = (int)row["AverageNumberOfTasks"];
//            result.NumberOfTasksInQueue = (int)row["NumberOfTasksInQueue"];
//            result.AverageWaitingTimeInQueue = (int)row["AverageWaitingTimeInQueue"];
//            return result;
//        }
        
//        private Topology getTopologyFromDbRow(DataRow row)
//        {
//            Topology topology = new Topology();
//            topology.idTopology = (int)row["idTopology"];
//            topology.Type = (string)row["Type"];
//            return topology;
//        }

//        public ICollection<ComplexNet> selectAllNets()
//        {

//            ICollection<ComplexNet> nets=new Collection<ComplexNet>();
//            string stringQuery = @"SELECT * FROM net,net_settings,topology WHERE net.idNet=net_settings.idNet and net.Topology_id=topology.idTopology;";

//            DataTable dt=getQuery(stringQuery);

//            foreach (DataRow row in dt.Rows)
//            {
//                Net net = getNetFromDbRow(row);
//                NetSettings netSettings = getNetSettingsFromDbRow(row);
//                Topology topology = getTopologyFromDbRow(row);


//                stringQuery = @"SELECT * FROM result WHERE result.idNet="+net.idNet+";";
//                DataTable dt2 = getQuery(stringQuery);
//                ICollection<Result> results=new Collection<Result>();

//                foreach (DataRow resultRow in dt2.Rows)
//                {
//                    Result result = getResultFromDbRow(resultRow);
//                    results.Add(result);
//                }

//                nets.Add(new ComplexNet(net,netSettings,results,topology));

//            }
//            return nets;
//        }

//        private int insertUpdateOnlyNet(Net net)
//        {
//            string stringQuery = @"SELECT * FROM net WHERE net.idNet="+net.idNet+";";

//            DataTable dt = getQuery(stringQuery);

//            if (dt.Rows.Count > 0)
//            {
//                stringQuery = @"UPDATE net SET Description="+net.Description+",Name="+net.Name+",Json="+net.Json+",Topology_id="+net.Topology_id+" WHERE net.idNet=" + net.idNet + ";";
//                getQuery(stringQuery);
//                return net.idNet;
//            }
//            else
//            {
//                stringQuery = @"INSERT INTO  net (Description ,Name ,Json ,Topology_id ) VALUES (" + net.Description + "," + net.Name + "," + net.Json + "," + net.Topology_id + ");";
//                getQuery(stringQuery);
        
//                stringQuery = @"SELECT  MAX(idNet) FROM net WHERE 1;";
//                dt=getQuery(stringQuery);
//                net.idNet = (int) dt.Rows[0]["MAX(idNet)"];

//                return net.idNet;
//            }

//        }

//        private int insertUpdateOnlyNetSettings(NetSettings netSettings)
//        {
//            string stringQuery = @"SELECT * FROM net_settings WHERE net_settings.idNet_Settings=" + netSettings.idNet_Settings + ";";

//            DataTable dt = getQuery(stringQuery);

//            if (dt.Rows.Count > 0)
//            {
//                stringQuery = @"UPDATE net_settings SET idNet="+netSettings.idNet +
//                    ",ThreadsCount="+netSettings.ThreadsCount +
//                    ",FrequencyTaskGen="+netSettings.FrequencyTaskGen +
//                    ",ModeTaskGen="+netSettings.ModeTaskGen +
//                    ",TimeTaskGen="+netSettings.TimeTaskGen + " WHERE net_settings.idNet_Settings=" + netSettings.idNet_Settings + ";";
//                getQuery(stringQuery);
//                return netSettings.idNet_Settings;
//            }
//            else
//            {
//                stringQuery =@"INSERT INTO `net_settings`(`idNet_Settings`, `idNet`, `ThreadsCount`, `FrequencyTaskGen`, `ModeTaskGen`, `TimeTaskGen`) 
//                                    VALUES ("+netSettings.idNet_Settings+","+netSettings.idNet+","+netSettings.ThreadsCount+","
//                                             +netSettings.FrequencyTaskGen+","+netSettings.ModeTaskGen+","+netSettings.TimeTaskGen+");";
//                getQuery(stringQuery);

//                stringQuery = @"SELECT  MAX(idNet_Settings) FROM net_settings WHERE 1;";
//                dt = getQuery(stringQuery);
//                netSettings.idNet = (int)dt.Rows[0]["MAX(idNet_Settings)"];

//                return netSettings.idNet_Settings;
//            }
//        }

//        public int insertResult(Result result)
//        {

//            DataTable dt = new DataTable();
//            string stringQuery = @"INSERT INTO result (`idResult`, `idNet`, `IntensityGen`, `IntensityService`, `AverageCountOfWorkNodes`, `AverageNumberOfTasks`, `NumberOfTasksInQueue`, `AverageWaitingTimeInQueue`) VALUES ("+
//                                                        result.idResult + "," + result.idNet + "," + result.IntensityGen + "," + result.IntensityService + ","
//                                                        + result.AverageCountOfWorkNodes + "," + result.AverageNumberOfTasks + "," + result.NumberOfTasksInQueue + "," + result.AverageWaitingTimeInQueue+ ");";
//             getQuery(stringQuery);

//            stringQuery = @"SELECT  MAX(idResult) FROM result WHERE 1;";
//                dt = getQuery(stringQuery);
//                result.idResult = (int)dt.Rows[0]["MAX(idResult)"];

//                return result.idResult;
//        }

//        public int insertUpdateNet(Net net,NetSettings netSettings)
//        {
//            netSettings.idNet = insertUpdateOnlyNet(net);
//            insertUpdateOnlyNetSettings(netSettings);

//            return netSettings.idNet;
//        }

    }
}